package com.thinkingstar.iads.datacenter.entity.dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.LinkedList;
import java.util.List;

import org.nutz.dao.Dao;
import org.nutz.dao.QueryResult;
import org.nutz.dao.Sqls;
import org.nutz.dao.pager.Pager;
import org.nutz.dao.sql.Sql;
import org.nutz.dao.sql.SqlCallback;
import org.nutz.ioc.loader.annotation.IocBean;
import org.nutz.lang.util.NutMap;

import com.thinkingstar.iads.common.serviceImpl.EntityRepositoryServiceImpl;
import com.thinkingstar.iads.datacenter.entity.DcProjectMentionInformation;
import com.thinkingstar.iads.datacenter.entity.DcTemplateVersion;

/**
 * @ClassName: DaoTemplateVersion 
 * @Description:数据中心_项目_合成模板库
 * @author 1157372219@qq.com 
 * @date 2018年3月19日   
 */
@IocBean(args = {"refer:dao"})
public class DaoTemplateVersion extends EntityRepositoryServiceImpl<DcTemplateVersion>{
	
	public DaoTemplateVersion(Dao dao) {
		super(dao);
	}
	
	//查询模板库
	public QueryResult findDesignTemplate(NutMap param){
		List <NutMap> list = null;
		QueryResult result = null;
		Pager pager = null;
		if(param == null){
			return null;
		}
		StringBuilder find_sql = new StringBuilder("select dctv.* , dpdp.project_design_phase_name ,"
				+ " dpdm.project_design_major_name , dpdc.project_design_content_name "
				+ " from dc_template_version dctv "
				+ " left join dc_project_design_phase dpdp on dctv.design_phase_id = dpdp.id "
				+ " left join dc_project_design_major dpdm on dctv.design_major_id = dpdm.id"
				+ " left join dc_project_design_content dpdc on dctv.design_content_id = dpdc.id "
				+ " where dctv.del_flag = '0' and dpdp.del_flag = '0'"
				+ " and dpdm.del_flag = '0' and dpdc.del_flag = '0' ");
		if(param.get("template_name") != null){
			find_sql.append(" and dctv.template_name like @template");
		}
		Sql sql = Sqls.create(find_sql.toString());
		if(param.get("template_name") != null){
			sql.params().set("template_name", "%"+param.get("template_name")+"%");
		}
		if(param.get("page_num") != null && param.get("page_size") != null){
			pager = new Pager(param.getInt("page_num"), param.getInt("page_size"));
			pager.setRecordCount(findDesignTemplateNum(param));
			sql.setPager(pager);
		}
		sql.setCallback(new SqlCallback() {
			@Override
			public Object invoke(Connection conn, ResultSet rs, Sql sql) throws SQLException {
			    List<NutMap> list = new ArrayList<>();
			    while (rs.next()){
			    	NutMap nutMap = new NutMap();
			        ResultSetMetaData metaData = rs.getMetaData();
			        int count = metaData.getColumnCount();
			        for (int i = count; i > 0; i--) {
			            String filed = metaData.getColumnName(i);
			            nutMap.put(filed, rs.getString(filed));
			        }
			        list.add(nutMap);
			    }
			    return list;
			}
		});
		dao().execute(sql);
		list = sql.getList(NutMap.class);
		return new QueryResult(list,pager);
	}
	
	//查询模板库数量
	public int findDesignTemplateNum(NutMap param){
		StringBuilder find_sql = new StringBuilder("select count(dctv.id) num "
				+ " from dc_template_version dctv "
				+ " left join dc_project_design_phase dpdp on dctv.design_phase_id = dpdp.id "
				+ " left join dc_project_design_major dpdm on dctv.design_major_id = dpdm.id"
				+ " left join dc_project_design_content dpdc on dctv.design_content_id = dpdc.id "
				+ " where dctv.del_flag = '0' and dpdp.del_flag = '0'"
				+ " and dpdm.del_flag = '0' and dpdc.del_flag = '0' ");
		if(param.get("template_name") != null){
			find_sql.append(" and dctv.template_name like @template");
		}
		Sql sql = Sqls.create(find_sql.toString());
		if(param.get("template_name") != null){
			sql.params().set("template_name", "%"+param.get("template_name")+"%");
		}
		sql.setCallback(new SqlCallback() {
			@Override
			public Object invoke(Connection conn, ResultSet rs, Sql sql) throws SQLException {
				int num = 0;
				while (rs.next()) {
					num = rs.getInt("num");
				}
				return num;
			}
		});
		dao().execute(sql);
		return sql.getInt();
	}
	
	//查询模板库所需的设计常量信息
	public Object findConstantDataByTemplate(NutMap param){
		StringBuilder find_sql = new StringBuilder(" select distinct(dcv.id) id , dcv.constant_table_code , dcv.constant_table_name ,"
				+ " (select id from dc_template_version where del_flag = '0' and id = @template_id and find_in_set(dcv.id,constant_data)) is_include "
				+ " from dc_constant_version dcv "
				+ " where dcv.del_flag = '0' and dcv.project_design_phase_id = @phase_id and dcv.project_design_content_id = @content_id ");
		Sql sql = Sqls.create(find_sql.toString());
		sql.params().set("template_id", param.get("template_id"));
		sql.params().set("phase_id", param.get("phase_id"));
		sql.params().set("content_id", param.get("content_id"));
		sql.setCallback(new SqlCallback() {
			@Override
			public Object invoke(Connection conn, ResultSet rs, Sql sql) throws SQLException {
				List <NutMap> result_list = new LinkedList<>();
				while (rs.next()) {
					NutMap nutMap = new NutMap();
					nutMap.put("id", rs.getString("id"));
					nutMap.put("constant_table_code", rs.getString("constant_table_code"));
					nutMap.put("constant_table_name", rs.getString("constant_table_name"));
					nutMap.put("is_include", rs.getString("is_include"));
					result_list.add(nutMap);
				}
				return result_list;
			}
		});
		dao().execute(sql);
		return sql.getList(NutMap.class);
	}
	
	//查询模板库所需的模板信息
	public Object findCompoundTemplateByTemplate(NutMap param){
		StringBuilder find_sql = new StringBuilder(" select distinct(dctv.id) id , dctv.template_code , dctv.template_name , "
				+ " (select id from dc_template_version where del_flag = '0' and id = @template_id and find_in_set(dctv.id,compound_template)) is_include "
				+ " from dc_compound_template__version dctv "
				+ " where dctv.del_flag = '0' and dctv.design_phase_id = @phase_id and dctv.design_content_id = @content_id ");
		Sql sql = Sqls.create(find_sql.toString());
		sql.params().set("template_id", param.get("template_id"));
		sql.params().set("phase_id", param.get("phase_id"));
		sql.params().set("content_id", param.get("content_id"));
		sql.setCallback(new SqlCallback() {
			@Override
			public Object invoke(Connection conn, ResultSet rs, Sql sql) throws SQLException {
				List <NutMap> list = new ArrayList<>();
				while (rs.next()) {
			    	NutMap nutMap = new NutMap();
			        ResultSetMetaData metaData = rs.getMetaData();
			        int count = metaData.getColumnCount();
			        for (int i = count; i > 0; i--) {
			            String filed = metaData.getColumnName(i);
			            nutMap.put(filed, rs.getString(filed));
			        }
			        list.add(nutMap);
				}
				return list;
			}
		});
		dao().execute(sql);
		return sql.getList(NutMap.class);
	}
	
	//查询提出资料模板库所需的模板信息
	public Object findPutForWardCompoundTemplateByTemplate(NutMap param){
		StringBuilder find_sql = new StringBuilder(" select distinct(dctv.id) id , dctv.template_code , dctv.template_name , "
				+ " (select id from dc_project_putford where del_flag = '0' and id = @template_id and find_in_set(dctv.id,compound_template)) is_include "
				+ " from dc_compound_template__version dctv "
				+ " where dctv.del_flag = '0' and dctv.design_phase_id = @phase_id and dctv.design_content_id = @content_id ");
		Sql sql = Sqls.create(find_sql.toString());
		sql.params().set("template_id", param.get("template_id"));
		sql.params().set("phase_id", param.get("phase_id"));
		sql.params().set("content_id", param.get("content_id"));
		sql.setCallback(new SqlCallback() {
			@Override
			public Object invoke(Connection conn, ResultSet rs, Sql sql) throws SQLException {
				List <NutMap> list = new ArrayList<>();
				while (rs.next()) {
			    	NutMap nutMap = new NutMap();
			        ResultSetMetaData metaData = rs.getMetaData();
			        int count = metaData.getColumnCount();
			        for (int i = count; i > 0; i--) {
			            String filed = metaData.getColumnName(i);
			            nutMap.put(filed, rs.getString(filed));
			        }
			        list.add(nutMap);
				}
				return list;
			}
		});
		dao().execute(sql);
		return sql.getList(NutMap.class);
	}
	//查询提出资料所需的设计单元信息
	public Object findPutForWardDesignCellDataByTemplate(NutMap param){
		StringBuilder find_sql = new StringBuilder(" select distinct(dpdc.id) id , dpdc.design_cell_table_code , dpdc.design_cell_table_name , "
				+ " (select id from dc_project_putford where del_flag = '0' and id = @template_id and find_in_set(dpdc.id,design_cell_data)) is_include "
				+ " from dc_project_design_cell dpdc "
				+ " where dpdc.del_flag = '0' and dpdc.project_design_phase_id = @phase_id and dpdc.project_design_content_id = @content_id ");
		Sql sql = Sqls.create(find_sql.toString());
		sql.params().set("template_id", param.get("template_id"));
		sql.params().set("phase_id", param.get("phase_id"));
		sql.params().set("content_id", param.get("content_id"));
		sql.setCallback(new SqlCallback() {
			@Override
			public Object invoke(Connection conn, ResultSet rs, Sql sql) throws SQLException {
				List <NutMap> result_list = new LinkedList<>();
				while (rs.next()) {
					NutMap nutMap = new NutMap();
					nutMap.put("id", rs.getString("id"));
					nutMap.put("design_cell_table_code", rs.getString("design_cell_table_code"));
					nutMap.put("design_cell_table_name", rs.getString("design_cell_table_name"));
					nutMap.put("is_include", rs.getString("is_include"));
					result_list.add(nutMap);
				}
				return result_list;
			}
		});
		dao().execute(sql);
		return sql.getList(NutMap.class);
	}


	//查询提出资料所需的提入资料信息
	public Object findPutForWardMentionDataByTemplate(NutMap param){
		StringBuilder find_sql = new StringBuilder(" select "
				+ " distinct(dpmi.id) id , dpmi.mention_information_code , dpmi.mention_information_name , "
				+ " (select dtv.id template_id from dc_project_putford dtv where dtv.del_flag = '0' and dtv.id = @template_id and find_in_set(dpmi.id , mention_data)) is_include "
				+ " from dc_project_mention_information_content_major dpmicm "
				+ " inner join dc_project_mention_information dpmi on dpmicm.project_mention_information_id = dpmi.id"
				+ " where dpmicm.del_flag = '0' and dpmi.del_flag = '0'"
				+ " and dpmicm.project_design_phase_id = @phase_id and dpmicm.content_id = @content_id  ");
		Sql sql = Sqls.create(find_sql.toString());
		sql.params().set("template_id", param.get("template_id"));
		sql.params().set("phase_id", param.get("phase_id"));
		sql.params().set("content_id", param.get("content_id"));
		sql.setCallback(new SqlCallback() {
			@Override
			public Object invoke(Connection conn, ResultSet rs, Sql sql) throws SQLException {
				List <NutMap> list = new LinkedList<>();
				while (rs.next()) {
					NutMap nutMap = new NutMap();
					nutMap.put("id", rs.getString("id"));
					nutMap.put("mention_information_code", rs.getString("mention_information_code"));
					nutMap.put("mention_information_name", rs.getString("mention_information_name"));
					nutMap.put("is_include", rs.getString("is_include"));
					list.add(nutMap);
				}
				return list;
			}
		});
		dao().execute(sql);
		return sql.getList(NutMap.class);
	}
	
	//查询模板库所需的设计单元信息
	public Object findDesignCellDataByTemplate(NutMap param){
		StringBuilder find_sql = new StringBuilder(" select distinct(dpdc.id) id , dpdc.design_cell_table_code , dpdc.design_cell_table_name , "
				+ " (select id from dc_template_version where del_flag = '0' and id = @template_id and find_in_set(dpdc.id,design_cell_data)) is_include "
				+ " from dc_project_design_cell dpdc "
				+ " where dpdc.del_flag = '0' and dpdc.project_design_phase_id = @phase_id and dpdc.project_design_content_id = @content_id ");
		Sql sql = Sqls.create(find_sql.toString());
		sql.params().set("template_id", param.get("template_id"));
		sql.params().set("phase_id", param.get("phase_id"));
		sql.params().set("content_id", param.get("content_id"));
		sql.setCallback(new SqlCallback() {
			@Override
			public Object invoke(Connection conn, ResultSet rs, Sql sql) throws SQLException {
				List <NutMap> result_list = new LinkedList<>();
				while (rs.next()) {
					NutMap nutMap = new NutMap();
					nutMap.put("id", rs.getString("id"));
					nutMap.put("design_cell_table_code", rs.getString("design_cell_table_code"));
					nutMap.put("design_cell_table_name", rs.getString("design_cell_table_name"));
					nutMap.put("is_include", rs.getString("is_include"));
					result_list.add(nutMap);
				}
				return result_list;
			}
		});
		dao().execute(sql);
		return sql.getList(NutMap.class);
	}
	
	
	//查询模板库所需的提入资料信息
	public Object findMentionDataByTemplate(NutMap param){
		StringBuilder find_sql = new StringBuilder(" select "
				+ " distinct(dpmi.id) id , dpmi.mention_information_code , dpmi.mention_information_name , "
				+ " (select dtv.id template_id from dc_template_version dtv where dtv.del_flag = '0' and dtv.id = @template_id and find_in_set(dpmi.id , mention_data)) is_include "
				+ " from dc_project_mention_information_content_major dpmicm "
				+ " inner join dc_project_mention_information dpmi on dpmicm.project_mention_information_id = dpmi.id"
				+ " where dpmicm.del_flag = '0' and dpmi.del_flag = '0'"
				+ " and dpmicm.project_design_phase_id = @phase_id and dpmicm.content_id = @content_id  ");
		Sql sql = Sqls.create(find_sql.toString());
		sql.params().set("template_id", param.get("template_id"));
		sql.params().set("phase_id", param.get("phase_id"));
		sql.params().set("content_id", param.get("content_id"));
		sql.setCallback(new SqlCallback() {
			@Override
			public Object invoke(Connection conn, ResultSet rs, Sql sql) throws SQLException {
				List <NutMap> list = new LinkedList<>();
				while (rs.next()) {
					NutMap nutMap = new NutMap();
					nutMap.put("id", rs.getString("id"));
					nutMap.put("mention_information_code", rs.getString("mention_information_code"));
					nutMap.put("mention_information_name", rs.getString("mention_information_name"));
					nutMap.put("is_include", rs.getString("is_include"));
					list.add(nutMap);
				}
				return list;
			}
		});
		dao().execute(sql);
		return sql.getList(NutMap.class);
	}

}
